﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using QL_DTTN.DAL;
namespace QL_DTTN.DTO.DTO
{
   public partial class ThiSinh
    {
       public ThiSinh getThiSinh(string soBaoDanh)
       {
           ThiSinh ts = new ThiSinh();
           bool kt = false;
           SqlDataProvider dp = new SqlDataProvider();
           System.Data.SqlClient.SqlDataReader sqlRr = GetObject(soBaoDanh, dp);
           if (sqlRr != null)
           {
               while (sqlRr.Read())
               {
                   if (!Convert.IsDBNull(sqlRr["SoBaoDanh"])) ts.SoBaoDanh = sqlRr["SoBaoDanh"].ToString();
                   if (!Convert.IsDBNull(sqlRr["HoTen"])) ts.HoTen = sqlRr["HoTen"].ToString();
                   if (!Convert.IsDBNull(sqlRr["SoCMND"])) ts.SoCMND = sqlRr["SoCMND"].ToString();
                   if (!Convert.IsDBNull(sqlRr["DiaChi"])) ts.DiaChi = sqlRr["DiaChi"].ToString();
                   if (!Convert.IsDBNull(sqlRr["MaTruong"])) ts.MaTruong = sqlRr["MaTruong"].ToString();                  
                   if (!Convert.IsDBNull(sqlRr["MaDoiTuong"])) ts.MaDoiTuong = sqlRr["MaDoiTuong"].ToString();

                   if (!Convert.IsDBNull(sqlRr["MaDanToc"])) ts.MaDanToc = Convert.ToInt16( sqlRr["MaDanToc"]);
                   if (!Convert.IsDBNull(sqlRr["MaHeDaoTao"])) ts.MaHeDaoTao = Convert.ToInt16( sqlRr["MaHeDaoTao"]);
                   if (!Convert.IsDBNull(sqlRr["MaKyThi"])) ts.MaKyThi = Convert.ToInt64( sqlRr["MaKyThi"]);
                   if (!Convert.IsDBNull(sqlRr["MaTinh"])) ts.MaTinh = Convert.ToInt16( sqlRr["MaTinh"]);

                   if (!Convert.IsDBNull(sqlRr["GioiTinh"])) ts.GioiTinh = Convert.ToBoolean(sqlRr["GioiTinh"]);

                   if (!Convert.IsDBNull(sqlRr["NgaySinh"])) ts.NgaySinh = Convert.ToDateTime(sqlRr["NgaySinh"]);
                   if (!Convert.IsDBNull(sqlRr["NgayVaoDang"])) ts.NgayVaoDang = Convert.ToDateTime(sqlRr["NgayVaoDang"]);
                   if (!Convert.IsDBNull(sqlRr["NgayVaoDoan"])) ts.NgayVaoDoan = Convert.ToDateTime(sqlRr["NgayVaoDoan"]);
                   kt = true;
               }
               dp.CloseConnection();
               if (kt) return ts;
               else return null;
           }
           else
           {
               return null;
           }
       }

       public bool Sua(ThiSinh thiSinh) 
       {
           string str = " update " + _tableName + " set HoTen=@pHoTen, DiaChi=@pDiaChi, GioiTinh=@pGioiTinh, SoCMND=@pSoCMND, NgaySinh=@pNgaySinh, NgayVaoDang=@pNgayVaoDang, NgayVaoDoan=@pNgayVaoDoan, MaDanToc=@pMaDanToc, MaDoiTuong=@pMaDoiTuong, MaHeDaoTao=@pMaHeDaoTao, MaKyThi=@pMaKyThi, MaTinh=@pMaTinh, MaTruong=@pMaTruong where SoBaoDanh=@pSoBaoDanh";
           SqlDataProvider dp = new SqlDataProvider();
           dp.AddParameter(new SqlParameter("@pSoBaoDanh", thiSinh.SoBaoDanh));
           dp.AddParameter(new SqlParameter("@pHoTen", thiSinh.HoTen));
           dp.AddParameter(new SqlParameter("@pDiaChi", thiSinh.DiaChi));
           dp.AddParameter(new SqlParameter("@pGioiTinh", thiSinh.GioiTinh));
           dp.AddParameter(new SqlParameter("@pSoCMND", thiSinh.SoCMND));
           dp.AddParameter(new SqlParameter("@pNgaySinh", thiSinh.NgaySinh));
           dp.AddParameter(new SqlParameter("@pNgayVaoDang", ((thiSinh.NgayVaoDang != new DateTime()) ? thiSinh.NgayVaoDang : Convert.DBNull)));
           dp.AddParameter(new SqlParameter("@pNgayVaoDoan", ((thiSinh.NgayVaoDoan != new DateTime()) ? thiSinh.NgayVaoDoan : Convert.DBNull)));
           dp.AddParameter(new SqlParameter("@pMaDanToc", thiSinh.MaDanToc));
           dp.AddParameter(new SqlParameter("@pMaDoiTuong", thiSinh.MaDoiTuong));
           dp.AddParameter(new SqlParameter("@pMaHeDaoTao", thiSinh.MaHeDaoTao));
           dp.AddParameter(new SqlParameter("@pMaKyThi", thiSinh.MaKyThi));
           dp.AddParameter(new SqlParameter("@pMaTinh", thiSinh.MaTinh));
           dp.AddParameter(new SqlParameter("@pMaTruong", thiSinh.MaTruong));
           return dp.RunSQL(str, true);
          }

       public DataTable tinThiSinh(string key,byte maTinh,string maTruong,byte maHeDaoTao,string maDoiTuong,long maKyThi)
       { 
           DataTable dt=new DataTable();
           string sql = " select * from  "+_tableName+" where HoTen like @pHoTen "+((maTruong=="")?"":" and MaTruong = @pMaTruong");

           return dt;
       }
       public double DiemDau(string soBaoDanh)
       {
           string sql = " select  Count(M.MaMonThi)*Max(D.DiemDau) ";
           sql += " from ThiSinh T left join ChiTietMonThi C on C.MaHeDaoTao=T.MaHeDaoTao ";
           sql += " inner join MonThi M on M.MaMonThi=C.MaMonThi and M.maKyThi=T.maKyThi";
           sql += " inner join DoiTuong D on D.MaDoiTuong=T.MaDoiTuong ";
           sql += " where T.SoBaoDanh = @pSoBaoDanh "; 
           SqlDataProvider dp = new SqlDataProvider();
           dp.AddParameter(new SqlParameter("@pSoBaoDanh", soBaoDanh));
           object kq = 0;
           dp.RunReturnSQL(sql, ref kq, true);
           return Convert.ToDouble(kq);
       }

       public DataTable DataDiemThiKQ(string key, short maTinh, string maTruong, short maHeDaoTao, long maKyThi, long maMonThi, short maTinhD, long maDiemThi, long maPhongThi)
       {
           string sql = "select  T.SoBaoDanh,HoTen,M2.TenMon,isNUll(K.KetQua,0) as KetQua  ";
           sql += " from " + _tableName + " T left join ChiTietMonThi C on C.MaHeDaoTao=T.MaHeDaoTao ";
           sql += " inner join MonThi M on M.MaMonThi=C.MaMonThi and M.maKyThi=T.maKyThi ";
           sql += " left join Mon M2 on M2.MaMon=M.MaMon ";
           sql += " left join KetQuaThi K on K.MaMon=M.MaMon and K.SoBaoDanh=T.SoBaoDanh ";
           sql += " left join Truong on Truong.MaTruong=T.MaTruong ";
           sql += " left join LichThi L on T.SoBaoDanh=L.SoBaoDanh and C.MaMonThi=L.MaMonThi ";
           sql += " left join PhongThi P on P.MaPhongThi=L.MaPhongThi ";
           sql += " left join DiemThi D on D.MaDiemThi=P.MaDiemThi ";
           sql += " where (HoTen Like @pKey or T.SoBaoDanh Like @pKey) ";
           sql += ((maTruong == "0") ? ((maTinh == 0) ? "" : " and Truong.MaTinh=@pMaTinh ") : " and T.MaTruong = @pMaTruong ");
           sql += ((maHeDaoTao == 0) ? "" : " and T.MaHeDaoTao = @pMaHeDaoTao ");
           sql += ((maKyThi == 0) ? "" : " and T.MaKyThi = @pMaKyThi ");
           sql += ((maMonThi == 0) ? "" : " and C.MaMonThi = @pMaMonThi ");
           sql += ((maPhongThi == 0) ? ((maDiemThi == 0) ? ((maTinhD == 0) ? "" : " and D.MaTinh=@pMaTinhD ") : " and D.MaDiemThi=@pMaDiemThi ") : " and P.MaPhongThi= @pMaPhongThi ");
           SqlDataProvider dp = new SqlDataProvider();
           dp.AddParameter(new SqlParameter("@pKey", "%" + key + "%"));
           dp.AddParameter(new SqlParameter("@pMaTruong", maTruong));
           dp.AddParameter(new SqlParameter("@pMaTinh", maTinh));
           dp.AddParameter(new SqlParameter("@pMaHeDaoTao", maHeDaoTao));
           dp.AddParameter(new SqlParameter("@pMaKyThi", maKyThi));
           dp.AddParameter(new SqlParameter("@pMaMonThi", maMonThi));
           dp.AddParameter(new SqlParameter("@pMaTinhD", maTinhD));
           dp.AddParameter(new SqlParameter("@pMaDiemThi", maDiemThi));
           dp.AddParameter(new SqlParameter("@pMaPhongThi", maPhongThi));
           return dp.ExDataTable(sql, true);
       }
   }
}
